/* 
 * Copyright 2014-2021 UME Framework, GNU General Public License 
 */
package com.u2framework.lib.tool.exp.tablediff;

import java.io.File;
import java.io.FileFilter;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.u2framework.lib.core.jdbc.JdbcDao;
import com.u2framework.lib.core.jdbc.impl.JdbcDaoImpl;
import com.u2framework.lib.core.util.StringUtil;
import com.u2framework.lib.tool.exp.table.TableStructExportSQL;
import com.u2framework.lib.tool.poi.CellWriter;
import com.u2framework.lib.tool.poi.ExcelAccessor;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

/**
 * 1.根据Excel输入的Table列表信息及数据库连接信息，查询数据字典获取表定义的结构并比较差分。
 *
 * @author SultansOfSwing & MaYue
 */
public class Excel2DbDiffExporter extends ExcelAccessor implements TableStructExportSQL {
    /**
     * logger
     */
    protected Logger logger = LoggerFactory.getLogger(Excel2DbDiffExporter.class);
    
    /**
     * 访问的Excel HSSF 对象
     */
    private Workbook book;
    /**
     * 访问的Excel文件名
     */
    private String excelFile;
    /**
     * 数据库种类
     */
    private String databaseType;
    /**
     * Dao实例
     */
    private List<JdbcDaoImpl> daoList = new ArrayList<>();
    /**
     * 数据字典查询SQL语句
     */
    private String tableDescQueryStr;
    /**
     * 数据字典查询SQL语句
     */
    private String tableListQueryStr;

    /**
     * Excel2DbExporter
     */
    public Excel2DbDiffExporter() {
    }

    /**
     * 构造函数
     * 
     * @param excelFile
     * @param tableDescQueryStr
     * @throws Exception
     */
    public void init(String excelFile) throws Exception {
        this.excelFile = excelFile;
        File file = new File(excelFile);
        book = loadExcel(file);
        List<DataSource> dsl = getDataSourceList();
        daoList = new ArrayList<>();
        for (DataSource ds : dsl) {
            JdbcDaoImpl dao = new JdbcDaoImpl(ds);
            daoList.add(dao);
        }
    }

    /**
     * expTableDefine
     * 
     * @param inputPath
     * @throws Throwable
     */
    public void expTableDefine(String inputPath) throws Throwable {
        for (File file : getFiles(inputPath)) {
            init(file.getAbsolutePath());
            this.createTableDocPages();
            logger.info(file.getName() + " has been prcoessd.");
        }
    }

    /**
     * 创建表定义的Excel页
     * 
     * @throws Throwable
     */
    protected void createTableDocPages() throws Throwable {
        List<String[]> tblConfigInfo = getTblConfigInfo();

        if (tblConfigInfo.size() > 100) {
            logger.info("处理的表个数【[" + tblConfigInfo.size() + "】过多，将花费较长时间，确认要继续吗？（继续处理请按'Y'键，终止请按其它键）");
            char res = (char) System.in.read();
            if (res != 'y' && res != 'Y') {
                logger.info("处理终止");
                return;
            }
            logger.info("处理继续...");
        }

        List<List<Object>> sumRows = new LinkedList<>();
        for (String[] e : tblConfigInfo) {
            String schemaName = e[0];
            String tableName = e[1];
            Map<String, String> param = new HashMap<String, String>();
            if (schemaName != null && !schemaName.trim().equals("") && !schemaName.trim().equals("*")) {
                param.put("varSchema", schemaName);
            }
            param.put("varTableId", tableName);

            List<LinkedHashMap<String, Excel2DbDiffBean>> colDescMapList = new ArrayList<>();

            for (int i = 0; i < daoList.size(); i++) {
                JdbcDao dao = daoList.get(i);
                List<Excel2DbDiffBean> tableDesc = dao.queryForObjectList(getTableDescQueryStr(), param, Excel2DbDiffBean.class);
                if (i == 0 && (tableDesc == null || tableDesc.size() == 0)) {
                    logger.info("No found table [" + tableName + "] in schema [" + schemaName + "]!");
                    continue;
                }
                if (tableDesc == null) {
                    tableDesc = new ArrayList<>(0);
                }

                LinkedHashMap<String, Excel2DbDiffBean> colDescMap = new LinkedHashMap<>();
                for (Excel2DbDiffBean colDesc : tableDesc) {
                    if (colDesc.getColId() != null) {
                        colDescMap.put(colDesc.getColId(), colDesc);
                    }
                }

                colDescMapList.add(colDescMap);
            }
            tableName = tableName.length() > 30 ? tableName.substring(0, 29) : tableName;

            createTableDocPage(sumRows, schemaName, tableName, colDescMapList);
        }
        if (!sumRows.isEmpty()) {
            List<Object> title = new ArrayList<>();
            title.add("schema");
            title.add("table");
            title.add("diff number");
            title.add("diff field");
            sumRows.add(0, title);
            super.createSheet(book, excelFile, "#SUM", sumRows);
        }
    }

    /**
     * getTblConfigInfo
     * 
     * @return
     */
    @SuppressWarnings("rawtypes")
    protected List<String[]> getTblConfigInfo() {
        Sheet sheet = book.getSheet("#TBLCONFIG");
        Coordinate coordinate = lookupDataCell(sheet, "SET SCHEMA");
        int row = coordinate.x + 1;
        int col = coordinate.y;

        Set<String> keySet = new HashSet<>();
        String key = getCellValueAsString(sheet, row, col).trim() + "-" + getCellValueAsString(sheet, row, col + 1).trim();
        while (!key.startsWith("-")) {
            row++;
            keySet.add(key);
            key = getCellValueAsString(sheet, row, col).trim() + "-" + getCellValueAsString(sheet, row, col + 1).trim();
        }

        for (String e : keySet) {
            if (e.endsWith("-*")) {
                String schema = e.split("-")[0];
                JdbcDao dao = daoList.get(0);
                Map<String, String> param = new HashMap<String, String>();
                param.put("varSchema", schema);
                List<LinkedHashMap> tableList = dao.queryForObjectList(getTableListQueryStr(), param, LinkedHashMap.class);
                if (tableList != null && !tableList.isEmpty()) {
                    for (LinkedHashMap map : tableList) {
                        Object value = map.get("TABLE_NAME");
                        if (value != null && !StringUtil.isEmpty(value.toString())) {
                            String table = value.toString();
                            key = schema + "-" + table;
                            keySet.add(key);
                        }
                    }
                }
            }
        }
        List<String[]> configs = new ArrayList<>();
        for (String e : keySet) {
            String[] seg = e.split("-");
            String schema = seg[0];
            String table = seg[1];
            if (!table.equals("*")) {
                configs.add(new String[] { schema, table });
            }
        }
        return configs;
    }

    /**
     * createDataRow
     * 
     * @param tableName
     * @param colIndex
     * @param colDesc
     * @param colDescMapList
     * @return
     */
    protected List<Object> createDataRow(String tableName, int colIndex, Excel2DbDiffBean colDesc, List<LinkedHashMap<String, Excel2DbDiffBean>> colDescMapList) {
        List<Object> row = new ArrayList<>();

        String colId = colDesc.getColId();
        String colName = StringUtil.isEmpty(colDesc.getColName()) ? colId : colDesc.getColName();
        row.add(colIndex);
        row.add(colName);
        row.add(colId);

        // String same = "相同";
        String nomatch = "【无此列】";
        boolean hasDiff = false;

        String type = getType(colDesc);
        row.add(type);
        for (int i = 1; i < colDescMapList.size(); i++) {
            LinkedHashMap<String, Excel2DbDiffBean> colDescMap = colDescMapList.get(i);
            if (colDescMap.containsKey(colId)) {
                String iType = getType(colDescMap.get(colId));
                if (!type.equals(iType)) {
                    hasDiff = true;
                    row.add("【" + iType + "】");
                } else {
                    row.add(iType);
                }
            } else {
                hasDiff = true;
                row.add(nomatch);
            }
        }

        String length = getLength(colDesc);
        row.add(length);
        for (int i = 1; i < colDescMapList.size(); i++) {
            LinkedHashMap<String, Excel2DbDiffBean> colDescMap = colDescMapList.get(i);
            if (colDescMap.containsKey(colId)) {
                String iLength = getLength(colDescMap.get(colId));
                if (!length.equals(iLength)) {
                    hasDiff = true;
                    row.add("【" + iLength + "】");
                } else {
                    row.add(iLength);
                }
            } else {
                hasDiff = true;
                row.add(nomatch);
            }
        }

        String pkFlag = getPkFlag(colDesc);
        row.add(pkFlag);
        for (int i = 1; i < colDescMapList.size(); i++) {
            LinkedHashMap<String, Excel2DbDiffBean> colDescMap = colDescMapList.get(i);
            if (colDescMap.containsKey(colId)) {
                String iPkFlag = getPkFlag(colDescMap.get(colId));
                if (!pkFlag.equals(iPkFlag)) {
                    hasDiff = true;
                    row.add("【" + iPkFlag + "】");
                } else {
                    row.add(iPkFlag);
                }
            } else {
                hasDiff = true;
                row.add(nomatch);
            }
        }

        String notNull = getNotNull(colDesc);
        row.add(notNull);
        for (int i = 1; i < colDescMapList.size(); i++) {
            LinkedHashMap<String, Excel2DbDiffBean> colDescMap = colDescMapList.get(i);
            if (colDescMap.containsKey(colId)) {
                String iNotNull = getNotNull(colDescMap.get(colId));
                if (!notNull.equals(iNotNull)) {
                    hasDiff = true;
                    row.add("【" + iNotNull + "】");
                } else {
                    row.add(iNotNull);
                }

            } else {
                hasDiff = true;
                row.add(nomatch);
            }
        }

        return hasDiff ? row : null;
    }

    /**
     * getType
     * 
     * @param colDesc
     * @return
     */
    protected String getType(Excel2DbDiffBean colDesc) {
        return colDesc.getDataType();
    }

    /**
     * getLength
     * 
     * @param colDesc
     * @return
     */
    protected String getLength(Excel2DbDiffBean colDesc) {
        String length = "";
        if (databaseType.equalsIgnoreCase("ORACLE")) {
            if (colDesc.getDataPrecision() != null && colDesc.getDataScale() != null) {
                if (colDesc.getDataScale().equals("0")) {
                    length = colDesc.getDataPrecision() + "";
                } else {
                    length = colDesc.getDataPrecision() + "," + colDesc.getDataScale();
                }
            } else if (colDesc.getDataPrecision() != null && colDesc.getDataScale() == null) {
                length = colDesc.getDataPrecision() + "";
            } else if (colDesc.getDataLength() != null) {
                length = colDesc.getDataLength() + "";
            }
        } else {
            if (colDesc.getDataPrecision() != null && colDesc.getDataScale() != null) {
                length = colDesc.getDataPrecision() + "," + colDesc.getDataScale();
            } else if (colDesc.getDataPrecision() != null) {
                length = colDesc.getDataPrecision() + "";
            } else if (colDesc.getDataLength() != null) {
                length = colDesc.getDataLength() + "";
            }
        }
        return length;
    }

    /**
     * getPkFlag
     * 
     * @param colDesc
     * @return
     */
    protected String getPkFlag(Excel2DbDiffBean colDesc) {
        return colDesc.getPkFlag().equals("1") ? "yes" : "no";
    }

    /**
     * getNotNull
     * 
     * @param colDesc
     * @return
     */
    protected String getNotNull(Excel2DbDiffBean colDesc) {
        return colDesc.getNotNull().equals("1") || colDesc.getNotNull().equalsIgnoreCase("Y") ? "yes" : "no";
    }

    /**
     * createTableDocPage
     * 
     * @param tableName
     * @param colDescMapList
     */
    protected void createTableDocPage(List<List<Object>> sumRows, String schemaName, String tableName, List<LinkedHashMap<String, Excel2DbDiffBean>> colDescMapList) {
        List<List<Object>> diffRows = new ArrayList<>();

        int colIndex = 1;
        Collection<Excel2DbDiffBean> tableDesc1 = colDescMapList.get(0).values();
        for (Excel2DbDiffBean colDesc1 : tableDesc1) {
            List<Object> row = createDataRow(tableName, colIndex, colDesc1, colDescMapList);
            colIndex++;
            if (row != null) {
                diffRows.add(row);
            }
        }

        if (!diffRows.isEmpty()) {
            List<List<Object>> rowsWithTitle = new ArrayList<>();
            rowsWithTitle.add(createTitleRow1(tableName, colDescMapList));
            rowsWithTitle.add(createTitleRow2(tableName, colDescMapList));
            rowsWithTitle.add(createTitleRow3(tableName, colDescMapList));
            rowsWithTitle.addAll(diffRows);

            List<Object> sumRow = new ArrayList<>();
            sumRow.add(schemaName);
            sumRow.add(tableName);
            sumRow.add(diffRows.size());
            for (List<Object> diffRow : diffRows) {
                if (diffRow.size() > 2) {
                    sumRow.add(diffRow.get(2));
                }
            }
            sumRows.add(sumRow);

            super.createSheet(book, excelFile, tableName, rowsWithTitle, new CellWriter<Object>() {
                // Title cell style
                CellStyle titleCellstyle10 = book.createCellStyle();
                CellStyle titleCellstyle20 = book.createCellStyle();

                @SuppressWarnings("deprecation")
                @Override
                public void write(Cell cell, Object value) {
                    int rowIndex = cell.getRowIndex();
                    if (rowIndex < 3) {
                        titleCellstyle10.setBorderTop(BorderStyle.THIN);
                        titleCellstyle10.setBorderBottom(BorderStyle.THIN);
                        titleCellstyle10.setBorderLeft(BorderStyle.THIN);
                        titleCellstyle10.setBorderRight(BorderStyle.THIN);
                        titleCellstyle10.setFillForegroundColor(IndexedColors.LIGHT_GREEN.index);
                        titleCellstyle10.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        cell.setCellStyle(titleCellstyle10);
                    }
                    if (value instanceof Number) {
                        cell.setCellType(CellType.NUMERIC);
                        cell.setCellValue(Double.valueOf(String.valueOf(value)));
                    } else {
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(String.valueOf(value));
                    }
                    if (value.toString().startsWith("【")) {
                        titleCellstyle20.setBorderTop(BorderStyle.THIN);
                        titleCellstyle20.setBorderBottom(BorderStyle.THIN);
                        titleCellstyle20.setBorderLeft(BorderStyle.THIN);
                        titleCellstyle20.setBorderRight(BorderStyle.THIN);
                        titleCellstyle20.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.index);
                        titleCellstyle20.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        cell.setCellStyle(titleCellstyle20);
                    }

                }
            });
            logger.info("Table [" + schemaName + "." + tableName + "] diff create.");
        }
    }

    /**
     * createTitleRow1
     * 
     * @param tableName
     * @param colDescMapList
     * @return
     */
    protected List<Object> createTitleRow1(String tableName, List<LinkedHashMap<String, Excel2DbDiffBean>> colDescMapList) {
        List<Object> row1 = new ArrayList<>();
        row1.add("表名");
        row1.add(tableName);
        return row1;
    }

    protected List<Object> createTitleRow2(String tableName, List<LinkedHashMap<String, Excel2DbDiffBean>> colDescMapList) {
        List<Object> row2 = new ArrayList<>();
        row2.add("*");
        row2.add("*");
        row2.add("*");
        int num = colDescMapList.size();
        for (int i = 0; i < num; i++) {
            row2.add("env" + (i + 1));
        }
        for (int i = 0; i < num; i++) {
            row2.add("env" + (i + 1));
        }
        for (int i = 0; i < num; i++) {
            row2.add("env" + (i + 1));
        }
        for (int i = 0; i < num; i++) {
            row2.add("env" + (i + 1));
        }
        return row2;
    }

    /**
     * createTitleRow3
     * 
     * @param tableName
     * @param colDescMapList
     * @return
     */
    protected List<Object> createTitleRow3(String tableName, List<LinkedHashMap<String, Excel2DbDiffBean>> colDescMapList) {
        List<Object> row2 = new ArrayList<>();
        row2.add("序号");
        row2.add("字段名");
        row2.add("字段ID");
        int num = colDescMapList.size();
        for (int i = 0; i < num; i++) {
            row2.add("数据类型");
        }
        for (int i = 0; i < num; i++) {
            row2.add("长度");
        }
        for (int i = 0; i < num; i++) {
            row2.add("主键");
        }
        for (int i = 0; i < num; i++) {
            row2.add("非空");
        }
        return row2;
    }

    /**
     * getDataSourceList
     * 
     * @return
     * @throws Exception
     */
    protected List<DataSource> getDataSourceList() throws Exception {
        List<DataSource> dsl = new ArrayList<>();
        Sheet sheet = book.getSheet("#DBCONFIG");

        Coordinate coordinate0 = lookupDataCell(sheet, "SET JDBC");
        int maxNum = 0;
        for (int i = 1; i <= 10; i++) {
            String value = getCellValueAsString(sheet, coordinate0.x, coordinate0.y + i);
            if (!StringUtil.isEmpty(value)) {
                maxNum++;
            }
        }

        String driver = null;
        String url = null;
        String user = null;
        String password = null;
        Coordinate coordinate1 = lookupDataCell(sheet, "DATABASE DRIVER");
        Coordinate coordinate2 = lookupDataCell(sheet, "DATABASE URL");
        Coordinate coordinate3 = lookupDataCell(sheet, "DATABASE USER");
        Coordinate coordinate4 = lookupDataCell(sheet, "DATABASE PASSWORD");

        for (int i = 1; i <= maxNum; i++) {
            driver = getCellValueAsString(sheet, coordinate1.x, coordinate1.y + i);
            url = getCellValueAsString(sheet, coordinate2.x, coordinate2.y + i);
            user = getCellValueAsString(sheet, coordinate3.x, coordinate3.y + i);
            password = getCellValueAsString(sheet, coordinate4.x, coordinate4.y + i);
            DataSource ds = buildDataSource(driver, url, user, password);
            dsl.add(ds);
            logger.info("Connected to:" + url);
        }
        return dsl;
    }

    /**
     * @return the databaseType
     */
    public String getDatabaseType() {
        return databaseType;
    }

    /**
     * @param databaseType the databaseType to set
     */
    public void setDatabaseType(String databaseType) {
        this.databaseType = databaseType;
    }

    /**
     * @param tableDescQueryStr the tableDescQueryStr to set
     */
    public void setTableDescQueryStr(String tableDescQueryStr) {
        this.tableDescQueryStr = tableDescQueryStr;
    }

    /**
     * @param inputPath
     * @return
     * @throws Exception
     */
    protected File[] getFiles(String inputPath) throws Exception {
        File[] files = new File(inputPath).listFiles(new FileFilter() {
            @Override
            public boolean accept(File e) {
                String name = e.getName().toLowerCase();
                return name.endsWith(".xls") || name.endsWith(".xlsx");
            }
        });
        return files;
    }

    /**
     * @return the tableDescQueryStr
     */
    public String getTableDescQueryStr() {
        if (!StringUtil.isEmpty(tableDescQueryStr)) {
            return tableDescQueryStr;
        }

        String type = databaseType.toUpperCase();
        if (type.equals("ORACLE")) {
            return TABLE_DESC_QUERY_FOR_ORACLE;
        } else {
            return TABLE_DESC_QUERY_FOR_MYSQL;
        }
    }

    /**
     * @return the tableListQueryStr
     */
    public String getTableListQueryStr() {
        if (!StringUtil.isEmpty(tableListQueryStr)) {
            return tableListQueryStr;
        }

        String type = databaseType.toUpperCase();
        if (type.equals("ORACLE")) {
            return TABLE_LIST_QUERY_FOR_ORACLE;
        }
        // TODO
        return null;
    }

    /**
     * buildDataSource
     * 
     * @param driver
     * @param url
     * @param user
     * @param password
     * @return
     * @throws Exception
     */
    public static DataSource buildDataSource(String driver, String url, String user, String password) throws Exception {
        HikariConfig hc = new HikariConfig();
        hc.setJdbcUrl(url);
        hc.setDriverClassName(driver);
        hc.setUsername(user);
        hc.setPassword(password);

        DataSource ds = new HikariDataSource(hc);
        return ds;
    }
}
